import tkinter as tk
from tkinter import ttk, messagebox
from tkcalendar import DateEntry
from datetime import datetime, timedelta
import pandas as pd
import os
import threading
import subprocess
from self_tools.ODPSHelper import ODPSHelper

# SQL常量
UNBIND_DRIVERS_SQL = """
    SELECT  driver_id
        ,driver_name
        ,driver_tel
        ,city
        ,brand
        ,team
        ,total_duration
        ,complete_count
        ,dt
FROM    DF_ch_146744.driverassessment
WHERE   dt = '{date}'
AND     total_duration != '0.0'
AND     driver_id NOT IN (
            SELECT  driver_id
            FROM    DF_ch_146744.wechat_friends_of_driver_manager
            WHERE   dt = '{original_date}'
            AND     wechat_friends_of_driver_manager.is_service_friend = '是'
        ) 
AND     (
            team LIKE '%K9用车(%'
            OR team LIKE '%公海%'
            OR team LIKE '%K9用车（%'
            OR team LIKE '%通行天下%'
            OR team LIKE '%万物互通%'
            OR team LIKE '%乐拼用车(%'
            OR team LIKE '%乐拼用车（%'
)
AND     (
            (
                city IN ('成都市','苏州市','重庆市','合肥市','太原市')
                AND brand = 'K9用车'
            )
            OR (
                city IN ('广州市')
                AND brand = '乐拼用车'
            )
)
AND     driver_id NOT IN (
            SELECT  driver_id
            FROM    DF_ch_146744.drivermanage_cube
            WHERE   dt = '{date}'
            AND     check_in_time LIKE '%{original_date}%'
            AND     (
                capacity_company LIKE '%K9用车(%'
                OR capacity_company LIKE '%公海%'
                OR capacity_company LIKE '%K9用车（%'
                OR capacity_company LIKE '%通行天下%'
                OR capacity_company LIKE '%万物互通%'
                OR capacity_company LIKE '%乐拼用车(%'
                OR capacity_company LIKE '%乐拼用车（%'
            )
            AND     (
                (
                    city IN ('成都市','苏州市','重庆市','合肥市','太原市')
                    AND brand = 'K9'
                )
                OR (
                    city IN ('广州市')
                    AND brand = 'lepin'
                )
            )
            AND     driver_id NOT IN (
                SELECT  driver_id
                FROM    DF_ch_146744.wechat_friends_of_driver_manager
                WHERE   dt = '{original_date}'
                AND     wechat_friends_of_driver_manager.is_service_friend = '是'
            ) 
        )
"""

NEW_DRIVERS_SQL = """
SELECT  driver_id
        ,dirver_name
        ,city
        ,brand
        ,dirver_phone
        ,capacity_company
        ,check_in_time
        ,dt
FROM    DF_ch_146744.drivermanage_cube
WHERE   dt = '{date}'
AND     check_in_time LIKE '%{original_date}%'
AND     (
            capacity_company LIKE '%K9用车(%'
            OR capacity_company LIKE '%公海%'
            OR capacity_company LIKE '%K9用车（%'
            OR capacity_company LIKE '%通行天下%'
            OR capacity_company LIKE '%万物互通%'
            OR capacity_company LIKE '%乐拼用车(%'
            OR capacity_company LIKE '%乐拼用车（%'
)
AND     (
            (
                city IN ('成都市','苏州市','重庆市','合肥市','太原市')
                AND brand = 'K9'
            )
            OR (
                city IN ('广州市')
                AND brand = 'lepin'
            )
)
AND     driver_id NOT IN (
            SELECT  driver_id
            FROM    DF_ch_146744.wechat_friends_of_driver_manager
            WHERE   dt = '{original_date}'
            AND     wechat_friends_of_driver_manager.is_service_friend = '是'
        )
"""

class DriverCheckApp:
    def __init__(self, root):
        self.root = root
        self.root.title("司机绑定检查工具")
        self.root.geometry("500x400")
        
        # 设置窗口样式
        self.style = ttk.Style()
        self.style.configure('Title.TLabel', font=('微软雅黑', 12, 'bold'))
        self.style.configure('Content.TLabel', font=('微软雅黑', 10))
        self.style.configure('Action.TButton', font=('微软雅黑', 10))
        
        # 确保data目录存在
        os.makedirs('./data', exist_ok=True)
        
        # 创建主框架
        main_frame = ttk.Frame(root, padding="20")
        main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        
        # 配置主框架的网格权重
        root.columnconfigure(0, weight=1)
        root.rowconfigure(0, weight=1)
        main_frame.columnconfigure(0, weight=1)
        
        # 标题
        title_label = ttk.Label(main_frame, text="司机绑定检查工具", style='Title.TLabel')
        title_label.grid(row=0, column=0, pady=(0, 20))
        
        # 日期选择器框架
        date_frame = ttk.LabelFrame(main_frame, text="日期选择", padding="10")
        date_frame.grid(row=1, column=0, sticky=(tk.W, tk.E), pady=(0, 20))
        date_frame.columnconfigure(1, weight=1)
        
        ttk.Label(date_frame, text="选择日期：", style='Content.TLabel').grid(row=0, column=0, padx=5)
        # 设置默认日期为前一天
        yesterday = datetime.now() - timedelta(days=1)
        self.date_picker = DateEntry(date_frame, width=15, background='darkblue',
                                   foreground='white', borderwidth=2,
                                   date_pattern='yyyy-mm-dd',
                                   year=yesterday.year,
                                   month=yesterday.month,
                                   day=yesterday.day,
                                   font=('微软雅黑', 10))
        self.date_picker.grid(row=0, column=1, padx=5, sticky=(tk.W, tk.E))
        
        # 按钮区域
        button_frame = ttk.LabelFrame(main_frame, text="操作", padding="10")
        button_frame.grid(row=2, column=0, sticky=(tk.W, tk.E), pady=(0, 20))
        button_frame.columnconfigure(0, weight=1)
        
        # 前一天出车未绑定司机按钮
        self.btn_unbind_drivers = ttk.Button(
            button_frame,
            text="出车未绑定司机",
            command=self.check_unbind_drivers,
            style='Action.TButton',
            width=30
        )
        self.btn_unbind_drivers.grid(row=0, column=0, pady=(5, 10), padx=20)
        
        # 前一天新入驻未绑定司机按钮
        self.btn_new_drivers = ttk.Button(
            button_frame,
            text="新入驻未绑定司机",
            command=self.check_new_drivers,
            style='Action.TButton',
            width=30
        )
        self.btn_new_drivers.grid(row=1, column=0, pady=(0, 5), padx=20)

        # 状态区域
        status_frame = ttk.LabelFrame(main_frame, text="状态", padding="10")
        status_frame.grid(row=3, column=0, sticky=(tk.W, tk.E))
        status_frame.columnconfigure(0, weight=1)
        
        self.progress_label = ttk.Label(status_frame, text="就绪", style='Content.TLabel')
        self.progress_label.grid(row=0, column=0, pady=5)
        self.progress_bar = ttk.Progressbar(status_frame, mode='indeterminate')
        
        self.odps_helper = ODPSHelper()

    def show_progress(self, show=True):
        """显示或隐藏进度条"""
        if show:
            self.progress_label.configure(text="正在查询数据，请稍候...")
            self.progress_bar.grid(row=1, column=0, pady=5, sticky=(tk.W, tk.E))
            self.progress_bar.start(10)
            self.btn_unbind_drivers.configure(state='disabled')
            self.btn_new_drivers.configure(state='disabled')
        else:
            self.progress_label.configure(text="就绪")
            self.progress_bar.stop()
            self.progress_bar.grid_remove()
            self.btn_unbind_drivers.configure(state='normal')
            self.btn_new_drivers.configure(state='normal')

    def open_data_folder(self):
        """打开数据文件夹"""
        data_path = os.path.abspath('./data')
        if os.name == 'nt':  # Windows
            os.startfile(data_path)
        else:  # Linux/Mac
            subprocess.run(['xdg-open', data_path])

    def get_previous_data(self, current_date, file_pattern):
        """获取指定日期之前的所有Excel数据"""
        data_path = './data'
        all_previous_data = []
        
        try:
            # 获取所有Excel文件
            excel_files = [f for f in os.listdir(data_path) if f.endswith('.xlsx') and file_pattern in f]
            
            for file in excel_files:
                # 从文件名中提取日期
                file_date_str = file.split('_')[0]
                try:
                    file_date = datetime.strptime(file_date_str, '%Y-%m-%d')
                    
                    # 只处理当前日期之前的文件
                    if file_date < current_date:
                        file_path = os.path.join(data_path, file)
                        df = pd.read_excel(file_path)
                        if 'driver_id' in df.columns:
                            # 只收集driver_id列
                            all_previous_data.extend(df['driver_id'].astype(str).tolist())
                except ValueError:
                    continue  # 跳过日期格式不正确的文件
            
            # 去除重复的driver_id
            return list(set(all_previous_data))
            
        except Exception as e:
            print(f"读取历史数据时出错: {str(e)}")
            return []

    def execute_query_and_save(self, sql, filename):
        """执行查询并保存结果到Excel"""
        try:
            self.root.after(0, self.show_progress, True)
            
            # 执行查询获取新数据
            data = self.odps_helper.run_sql(sql)
            pd_data = self.odps_helper.init_panda_data(data)
            current_df = pd.DataFrame(pd_data)
            
            if not current_df.empty and 'driver_id' in current_df.columns:
                # 获取当前日期
                current_date = datetime.strptime(filename.split('_')[0], '%Y-%m-%d')
                
                # 确定文件模式（出车未绑定或新入驻未绑定）
                file_pattern = '出车未绑定司机' if '出车未绑定' in filename else '新入驻未绑定司机'
                
                # 获取历史数据中的driver_id列表
                previous_driver_ids = self.get_previous_data(current_date, file_pattern)
                
                if previous_driver_ids:
                    # 确保driver_id列的类型一致
                    current_df['driver_id'] = current_df['driver_id'].astype(str)
                    # 过滤掉之前出现过的driver_id
                    current_df = current_df[~current_df['driver_id'].isin(previous_driver_ids)]
                    
                    # 记录被过滤掉的数量
                    filtered_count = len(pd_data) - len(current_df)
                
            # 保存新数据
            output_path = f'./data/{filename}'
            if not current_df.empty:
                current_df.to_excel(output_path, index=False)
                message = f"数据已保存到: {output_path}\n"
                message += f"共发现 {len(current_df)} 条新数据"
                if 'filtered_count' in locals():
                    message += f"\n过滤掉 {filtered_count} 条重复数据"
                self.root.after(0, messagebox.showinfo, "成功", message)
            else:
                self.root.after(0, messagebox.showinfo, "提示", "没有发现新数据")
            
            self.root.after(100, self.open_data_folder)  # 延迟100ms打开文件夹
            
        except Exception as e:
            self.root.after(0, messagebox.showerror, "错误", f"处理数据时出错: {str(e)}")
        finally:
            self.root.after(0, self.show_progress, False)

    def run_query_in_thread(self, sql, filename):
        """在新线程中运行查询"""
        thread = threading.Thread(target=self.execute_query_and_save, args=(sql, filename))
        thread.daemon = True
        thread.start()

    def check_unbind_drivers(self):
        selected_date = self.date_picker.get_date()
        date_str = selected_date.strftime('%Y-%m-%d')
        formatted_date = date_str.replace("-", "")
        
        sql = UNBIND_DRIVERS_SQL.format(
            date=formatted_date,
            original_date=date_str
        )
        self.run_query_in_thread(sql, f'{date_str}_出车未绑定司机.xlsx')

    def check_new_drivers(self):
        selected_date = self.date_picker.get_date()
        date_str = selected_date.strftime('%Y-%m-%d')
        formatted_date = date_str.replace("-", "")
        
        sql = NEW_DRIVERS_SQL.format(
            date=formatted_date,
            original_date=date_str
        )
        self.run_query_in_thread(sql, f'{date_str}_新入驻未绑定司机.xlsx')

if __name__ == "__main__":
    root = tk.Tk()
    app = DriverCheckApp(root)
    root.mainloop()